#!/usr/bin/env python3
"""
查询数据库中的文件上传记录脚本
使用SQLAlchemy ORM查询，与项目使用的方式一致
"""
import os
import sys
from datetime import datetime

# 添加项目根目录到Python路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

# 导入项目的数据库和模型
from app.core.database import engine, SessionLocal
from app.models.upload import FileUpload, Base

def query_uploads():
    """查询并显示所有上传文件记录，包括三种分析状态"""
    db = SessionLocal()
    try:
        # 确保数据库表存在
        Base.metadata.create_all(bind=engine)
        
        # 查询所有上传记录
        print("\n上传文件记录:")
        print("-" * 180)
        print(f"{'ID':<5} | {'文件名':<30} | {'原始文件名':<30} | {'大小(KB)':<10} | {'上传时间':<20} | {'离场/停留识别':<15} | {'背景/活动区分':<15} | {'异常检测':<15}")
        print("-" * 180)
        
        # 使用SQLAlchemy查询所有记录
        records = db.query(FileUpload).order_by(FileUpload.upload_time.desc()).all()
        
        if not records:
            print("  没有找到上传记录")
        else:
            for record in records:
                # 格式化时间和大小
                formatted_time = record.upload_time.strftime("%Y-%m-%d %H:%M:%S")
                size_kb = f"{record.file_size/1024:.2f}"
                # 截断长文件名
                short_filename = record.filename[:27] + "..." if len(record.filename) > 30 else record.filename
                short_original = record.original_filename[:27] + "..." if len(record.original_filename) > 30 else record.original_filename
                # 获取分析状态
                departure_status = getattr(record, 'departure_retention_status', 'N/A')
                background_status = getattr(record, 'background_activity_status', 'N/A')
                anomaly_status = getattr(record, 'anomaly_detection_status', 'N/A')
                print(f"{record.id:<5} | {short_filename:<30} | {short_original:<30} | {size_kb:<10} | {formatted_time:<20} | {departure_status:<15} | {background_status:<15} | {anomaly_status:<15}")
        
        print("-" * 180)
        print(f"\n总计: {len(records)} 条记录")
        
        # 查询文件类型统计
        print("\n文件类型统计:")
        # 手动统计文件类型
        type_counts = {}
        for record in records:
            file_type = record.file_type or 'Unknown'
            type_counts[file_type] = type_counts.get(file_type, 0) + 1
        
        # 按数量排序并显示
        for file_type, count in sorted(type_counts.items(), key=lambda x: x[1], reverse=True):
            print(f"  {file_type:<20} | {count} 个文件")
            
    except Exception as e:
        print(f"数据库查询错误: {e}")
        sys.exit(1)
    finally:
        db.close()

def query_by_id(record_id):
    """根据ID查询特定记录的详细信息，包括三种分析状态"""
    db = SessionLocal()
    try:
        # 使用SQLAlchemy查询特定记录
        record = db.query(FileUpload).filter(FileUpload.id == record_id).first()
        
        if not record:
            print(f"\n未找到ID为 {record_id} 的记录")
            return
        
        print(f"\nID为 {record_id} 的详细信息:")
        print("-" * 60)
        
        # 获取记录的所有属性
        fields = {
            'id': record.id,
            'filename': record.filename,
            'original_filename': record.original_filename,
            'file_path': record.file_path,
            'file_size': f"{record.file_size} 字节 ({record.file_size/1024:.2f} KB)",
            'file_type': record.file_type or 'Unknown',
            'upload_time': record.upload_time.strftime("%Y-%m-%d %H:%M:%S"),
            'status': record.status,
            '离场/停留识别状态': getattr(record, 'departure_retention_status', 'N/A'),
            '背景/活动区分状态': getattr(record, 'background_activity_status', 'N/A'),
            '异常检测状态': getattr(record, 'anomaly_detection_status', 'N/A')
        }
        
        for field_name, value in fields.items():
            print(f"{field_name:<20}: {value}")
        
        print("-" * 60)
        
    except Exception as e:
        print(f"数据库查询错误: {e}")
    finally:
        db.close()

def main():
    """主函数"""
    print("=" * 60)
    print("文件上传记录查询工具")
    print("=" * 60)
    
    # 获取数据库URL
    from app.core.database import DATABASE_URL
    print(f"数据库URL: {DATABASE_URL}")
    print("数据库类型: SQLAlchemy ORM (SQLite)")
    print("使用模型: FileUpload")
    
    # 处理命令行参数
    if len(sys.argv) > 1:
        # 如果提供了ID参数，查询特定记录
        try:
            record_id = int(sys.argv[1])
            query_by_id(record_id)
        except ValueError:
            print("错误: ID必须是数字")
            print("使用方法:")
            print("  python query_uploads.py         # 查看所有记录")
            print("  python query_uploads.py <id>    # 查看特定ID的详细信息")
            sys.exit(1)
    else:
        # 否则显示所有记录
        query_uploads()

if __name__ == "__main__":
    main()